USE carddb;

ALTER TABLE T_add_money 
ADD CHECK (the_money>=50 AND the_money<=200);
--查询所有饭卡信息；
SELECT * FROM T_card;

--查询所有饭卡的余额之和；
SELECT SUM(curr_money) FROM T_card;


--查询某日所有饭卡的充值金额之和(比如2011年6月1号总共充值金额是多少元)
SELECT SUM(the_money) FROM t_add_money 
WHERE  DATE_FORMAT(register_date,'%Y-%m-%d')='2011-04-17';

--查询某饭卡在2011年3月到2011年6月之间的消费金额
SELECT SUM(The_Money)FROM t_consume_money WHERE YEAR(Register_Date)=2011 
AND MONTH(Register_Date)BETWEEN 3 AND 6 AND Card_Id=2;


----------存储过程
CREATE PROCEDURE IsAbleToCosume
 @cardId INT,
 @thisCosume money
AS
BEGIN
  DECLARE @cur_Money money;
SET @cur_Money=(SELECT curr_money FROM t_card WHERE card_id=@cardId)
print @curMoney

IF(@curMoney>=@thisCosume )
RETURN 'true'
ELSE
 RETURN 'false' 
END


 USE stores;
/*
 向每个表插入条测试数据；
*/
   INSERT T_suppliers VALUES 
        ('1','人民商行');
 INSERT T_suppliers VALUES 
        ('2','华锐风电');
 INSERT T_suppliers VALUES 
        ('3','江淮汽车');
INSERT T_goods VALUES
     ('笔记本',3200,'2','湖南长沙' );
INSERT T_goods VALUES
     ('台式电脑',3200,'2','湖南株洲' )
INSERT T_goods VALUES
     ('空调',3200,'3','广东珠海' )


INSERT T_orders VALUES
   ('1001',3,700,3200)；
INSERT T_orders VALUES
   ('1003',4,600,3400)
INSERT T_orders VALUES
   ('1004',5,700,3600)


UPDATE T_Goods
  SET Goods_price=Goods_price*0.9,Goods_name ='惠普笔记本1'
  WHERE Goods_id = '1'; 
  /*	查询所有名称包含“笔记本”的商品编号、商品名称、商品价格；
*/
SELECT Goods_id,Goods_name,Goods_price
 FROM T_Goods
  WHERE Goods_name  LIKE'%笔记本';
  /*
	查询出订购了供应商名称为“大金科技”的商品名称、订购数量、商品总价；

    知识点：联合查询。

*/
 SELECT T_goods.Goods_name,T_orders.Quantity,
           T_orders.Total_price
   FROM T_goods,T_orders,T_suppliers
    WHERE T_goods.Goods_id=T_orders.Goods_id
      AND 
         T_goods.Supp_code =T_Suppliers.Supp_code 
         AND T_suppliers.Supp_name='大金科技'
/*
	创建存储过程P_stored_proc，指定商品编号，
    查询该商品的订单信息；
*/
 CREATE PROCEDURE search_good

     @good_name VARCHAR(50)
 AS
   SELECT T_goods.Goods_id
   FROM T_goods,T_orders
   WHERE T_goods.Goods_id=T_orders.Goods_id
          AND
         T_goods.Goods_name=@good_name

删除语句：
DROP PROCEDURE P_stored_proc
  
  USE  bookdb;
  
//向每个表中插入条测试数据
INSERT INTO T_card VALUES('001','李四','湖南省长沙市天心区','1351111111');
INSERT INTO T_card VALUES('002','刘五','湖南省长沙市岳麓区','1352222222');
INSERT INTO T_card VALUES('003','朱六','湖南省长沙市青园区','1343333333');

INSERT INTO T_book VALUES('01','红楼梦','曹雪芹','35','5','5');
INSERT INTO T_book VALUES('02','西游记','吴承恩','40','10','10');
INSERT INTO T_book VALUES('03','三国演义','罗贯中','45','15','15');

INSERT INTO T_borrow VALUES('0001','01','001','2010-10-31','2010-12-02');
INSERT INTO T_borrow VALUES('0002','02','002','2009-10-31','2010-11-02');
INSERT INTO T_borrow VALUES('0003','03','003','2010-10-31','2010-12-08');

//将“李”姓作者的所有图书单价下调%
UPDATE T_book SET Price=Price*0.9
		WHERE Author LIKE '曹%';

//查询日期在-10-31到-11-31之间的图书信息
SELECT * FROM T_book
	WHERE Book_no IN(SELECT Book_no FROM T_borrow WHERE Borrow_date>='2010-10-31'
		AND Return_date<='2010-11-31');

//查询还没有还书的借书人姓名
SELECT Card_name FROM T_card 
	WHERE Card_no IN (SELECT Card_no FROM T_borrow WHERE Return_date=NULL);

//查询手机号“”开头的所有借书人姓名21
SELECT Card_name FROM T_card WHERE 
Mobile LIKE'135%';

//编写视图查询库存数小于册的图书信息
CREATE VIEW V_book 
  AS SELECT * FROM T_book WHERE Qty<'10';

//编写存储过程，查询指定借阅者的借阅次数
CREATE PROCEDURE P_borrow_book
   @Card_name VARCHAR(30),
	@Loan_qty INT 
AS
SELECT @Loan_qty=Loan_qty FROM T_book WHERE @Card_name=Card_name;

向每个表插入3条测试数据；
查询出所有选修了“数据库原理”课程的学生学号、姓名和籍贯；
USE studentdb;
SELECT stud_id,stud_name,Birth_place 
FROM t_student,t_course
WHERE 
t_student.Course_id=t_course.Course_id
AND
t_course.Course_name='A'


删除所有选修“日语”的同学的选课记录；
DELETE FROM  t_score WHERE course_id='C';
查询出“数据库原理”这门课的最高成绩；
SELECT MAX(Score) FROM t_score
WHERE course_id='A';
查询有哪些课程没有被任何同学选修；
SELECT course_name FROM `t_course`
WHERE NOT IS SELECT;
创建视图，查询指定课程名称的平均成绩。
  CREATE VIEW V_score
  AS SELECT AVG(score) FROM T_score
WHERE course_id='A';
  
  
  